using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Users {
	class UserMessageDA {

        private const string SP_CountAll = "dbo.UserMessage_CountAll";
        private const string SP_CountUnReadMessagesInDay = "dbo.UserMessage_CountUnReadMessagesInDay";
        private const string SP_CountUnReplyMessagesFromUser1ToUser2 = "dbo.UserMessage_CountUnReplyMessagesFromUser1ToUser2";
        private const string SP_CountConversationByUserKey = "dbo.UserMessage_CountConversationByUserKey";
        private const string SP_CountConversationByUserKeyAndStatus = "dbo.UserMessage_CountConversationByUserKeyAndStatus";
        private const string SP_DeleteConversationByUserKeyAndConversationID = "dbo.UserMessage_DeleteConversationByUserKeyAndConversationID";
        private const string SP_GetConversationByUserKeyAndStatusWithPaging = "dbo.UserMessage_GetConversationByUserKeyAndStatusWithPaging";
        private const string SP_GetConversationByUserKeyWithPaging = "dbo.UserMessage_GetConversationByUserKeyWithPaging";
        private const string SP_GetConversationByConversationIDAndUserKey = "dbo.UserMessage_GetConversationByConversationIDAndUserKey";
        private const string SP_GetConversationMessagesByConversationIDAndUserKey = "dbo.UserMessage_GetConversationMessagesByConversationIDAndUserKey";
        private const string SP_InsertConversation = "dbo.UserMessage_InsertConversation";
        private const string SP_InsertMessage = "dbo.UserMessage_InsertMessage";
        private const string SP_ReceiverDeleteMessage = "dbo.UserMessage_ReceiverDeleteMessage";
        private const string SP_SenderDeleteMessage = "dbo.UserMessage_SenderDeleteMessage";
        private const string SP_UpdateStatus = "dbo.UserMessage_UpdateStatus";

		public static UserMessageEnt CreateFromReader(IDataReader reader) {
			UserMessageEnt item = new UserMessageEnt();
			item.ID = ConvertUtils.ToLong(reader["ID"]);
			item.ConversationID = ConvertUtils.ToLong(reader["ConversationID"]);
			item.SentUserKey = ConvertUtils.ToString(reader["SentUserKey"]);
			item.ReceivedUserKey = ConvertUtils.ToString(reader["ReceivedUserKey"]);
			item.Title = ConvertUtils.ToString(reader["Title"]);
			item.Content = ConvertUtils.ToString(reader["Content"]);
			item.Status = (EUserMessageStatus)ConvertUtils.ToByte(reader["Status"]);
			item.Type = (EUserMessageType)ConvertUtils.ToByte(reader["Type"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.SenderDeleted = ConvertUtils.ToBool(reader["SenderDeleted"]);
			item.ReceiverDeleted = ConvertUtils.ToBool(reader["ReceiverDeleted"]);
            item.SenderName = ConvertUtils.ToString(reader["SenderName"]);
            item.SenderGender = (EUserGender)ConvertUtils.ToByte(reader["SenderGender"]);
            item.SenderAvatarFileName = ConvertUtils.ToString(reader["SenderAvatarFileName"]);
            item.SenderAvatarImageStoreID = ConvertUtils.ToInt(reader["SenderAvatarImageStoreID"]);
            item.SenderAvatarStatus = (EUserAvatarStatus)ConvertUtils.ToByte(reader["SenderAvatarStatus"]);

			return item;
		}

		public static int DeleteConversationByUserKeyAndConversationID(string userKey, long conversationID, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_DeleteConversationByUserKeyAndConversationID)) {
				db.AddParameter(cmd, "UserKey", userKey);
				db.AddParameter(cmd, "ConversationID", conversationID);
				db.AddParameterOuter(cmd, "Result", DbType.Boolean);
				if (dbCon != null && dbTrans != null) {
					db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    result = ConvertUtils.ToInt(cmd.Parameters["Result"].Value);
				}
				else {
					db.ExecuteNonQuery(cmd);
                    result = ConvertUtils.ToInt(cmd.Parameters["Result"].Value);
				}
			}
			return result;
		}

        public static long CountAll(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountAll)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int CountUnReadMessagesInDay(string userKey, DateTime startTime, DateTime endTime, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountUnReadMessagesInDay)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "StartTime", startTime);
                db.AddParameter(cmd, "EndTime", endTime);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int CountUnReplyMessagesFromUser1ToUser2(string userKey1, string userKey2, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountUnReplyMessagesFromUser1ToUser2)) {
                db.AddParameter(cmd, "UserKey1", userKey1);
                db.AddParameter(cmd, "UserKey2", userKey2);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int CountConversationByUserKey(string userKey , string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountConversationByUserKey)) {
                db.AddParameter(cmd, "UserKey", userKey);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int CountConversationByUserKeyAndStatus(string userKey, EUserMessageStatus status, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountConversationByUserKeyAndStatus)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "Status", ConvertUtils.ToBool((byte)status));
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static List<UserMessageEnt> GetConversationByUserKeyAndStatusWithPaging(string userKey, EUserMessageStatus status, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserMessageEnt> result = new List<UserMessageEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetConversationByUserKeyAndStatusWithPaging)) {
				db.AddParameter(cmd, "UserKey", userKey);
				db.AddParameter(cmd, "Status", Convert.ToBoolean((byte)status));
				db.AddParameter(cmd, "StartRow", startRow);
				db.AddParameter(cmd, "EndRow", endRow);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static List<UserMessageEnt> GetConversationByUserKeyWithPaging(string userKey, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserMessageEnt> result = new List<UserMessageEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetConversationByUserKeyWithPaging)) {
				db.AddParameter(cmd, "UserKey", userKey);
				db.AddParameter(cmd, "StartRow", startRow);
				db.AddParameter(cmd, "EndRow", endRow);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static UserMessageEnt GetConversationByConversationIDAndUserKey(string userKey, long conversationID, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            UserMessageEnt result = null;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetConversationByConversationIDAndUserKey)) {
                db.AddParameter(cmd, "UserKey", userKey);
                db.AddParameter(cmd, "ConversationID", conversationID);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result = CreateFromReader(reader);
                    }
                }
            }
            return result;
        }

        public static List<UserMessageEnt> GetConversationMessagesByConversationIDAndUserKey(string userKey, long conversationID, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserMessageEnt> result = new List<UserMessageEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetConversationMessagesByConversationIDAndUserKey)) {
				db.AddParameter(cmd, "UserKey", userKey);
				db.AddParameter(cmd, "ConversationID", conversationID);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static int InsertConversation(UserMessageEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_InsertConversation)) {
				db.AddParameterOuter(cmd, "ID", DbType.Int64);
				db.AddParameter(cmd, "SentUserKey", item.SentUserKey);
				db.AddParameter(cmd, "ReceivedUserKey", item.ReceivedUserKey);
				db.AddParameter(cmd, "Title", item.Title);
				db.AddParameter(cmd, "Content", item.Content);
				db.AddParameter(cmd, "Status", item.Status);
				db.AddParameter(cmd, "Type", item.Type);
				db.AddParameter(cmd, "SenderDeleted", item.SenderDeleted);
				db.AddParameter(cmd, "ReceiverDeleted", item.ReceiverDeleted);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
					item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
                    item.ConversationID = item.ID;
				}
				else {
					result = db.ExecuteNonQuery(cmd);
					item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
                    item.ConversationID = item.ID;
				}
			}
			return result;
		}

        public static int InsertMessage(UserMessageEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_InsertMessage)) {
				db.AddParameterOuter(cmd, "ID", DbType.Int64);
				db.AddParameter(cmd, "ConversationID", item.ConversationID);
				db.AddParameter(cmd, "SentUserKey", item.SentUserKey);
				db.AddParameter(cmd, "ReceivedUserKey", item.ReceivedUserKey);
				db.AddParameter(cmd, "Title", item.Title);
				db.AddParameter(cmd, "Content", item.Content);
				db.AddParameter(cmd, "Status", item.Status);
				db.AddParameter(cmd, "Type", item.Type);
				db.AddParameter(cmd, "SenderDeleted", item.SenderDeleted);
				db.AddParameter(cmd, "ReceiverDeleted", item.ReceiverDeleted);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
					item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
					item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
				}
			}
			return result;
		}

        public static int ReceiverDeleteMessage(long messageID, string receivedUserKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_ReceiverDeleteMessage)) {
				db.AddParameter(cmd, "ID", messageID);
				db.AddParameter(cmd, "ReceivedUserKey", receivedUserKey);
				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int SenderDeleteMessage(long messageID, string sentUserKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_SenderDeleteMessage)) {
				db.AddParameter(cmd, "ID", messageID);
				db.AddParameter(cmd, "SentUserKey", sentUserKey);
				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateStatus(string receiverUserKey, long conversationID, EUserMessageStatus status, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateStatus)) {
				db.AddParameter(cmd, "ConversationID", conversationID);
                db.AddParameter(cmd, "ReceivedUserKey", receiverUserKey);
				db.AddParameter(cmd, "Status", status);
				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

	}
}